﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OracleClient;
using System.Data.OleDb;
using System.Data.Odbc;
using MySql.Data.MySqlClient;
using System.Reflection;

namespace DyllonSoft.DBManager
{
    /// <summary>
    /// 数据库管理类
    /// </summary>
    public class DBManager
    {
        #region 私有字段
        /// <summary>
        /// 数据库连接信息
        /// </summary>
        protected DBConnectionInfo _connectionInfo;
        #endregion

        #region 只读属性
        /// <summary>
        /// 数据库连接信息
        /// </summary>
        public virtual DBConnectionInfo ConnectionInfo 
        { 
            get 
            {
                return _connectionInfo;
            } 
        }
        #endregion

        #region 构造方法
        /// <summary>
        /// 构造方法
        /// </summary>
        public DBManager()
        {
            _connectionInfo = DBConnectionInfo.GetDBConnectionInfo();
        }
        /// <summary>
        /// 构造方法
        /// </summary>
        /// <param name="connInfo">数据库连接信息</param>
        public DBManager(DBConnectionInfo connInfo)
        {
            _connectionInfo = connInfo;
        }
        /// <summary>
        /// 构造方法
        /// </summary>
        /// <param name="connStr">数据库连接字符串</param>
        /// <param name="connType">数据库连接类型</param>
        public DBManager(string connStr, DBConnectionType connType)
        {
            _connectionInfo = Activator.CreateInstance(typeof(DBConnectionInfo), connStr, connType) as DBConnectionInfo;
        }
        #endregion

        #region SetCommandParams
        /// <summary>
        /// 设置继承IDbCommand接口的对象实例的参数
        /// </summary>
        /// <param name="cmd">要设置的继承IDbCommand接口的对象实例</param>
        /// <param name="parameters">继承IDbDataParameter接口的对象实例的参数列表</param>
        private void SetCommandParams(IDbCommand cmd, IDataParameter[] parameters)
        {
            if (parameters != null)
            {
                foreach (IDataParameter param in parameters)
                {
                    cmd.Parameters.Add(param);
                }
            }
        }
        #endregion

        #region ExecuteNonQuery
        /// <summary>
        /// 执行一个带参数的非Select的查询
        /// </summary>
        /// <param name="sqlStr">Sql语句或存储过程名字</param>
        /// <param name="parameters">继承IDbDataParameter接口的对象实例的参数列表</param>
        /// <param name="cmdType">Command的类型</param>
        /// <returns>对Update、Insert、Delete操作返回影响到的行数，其他情况为-1</returns>
        public int ExecuteNonQuery(string sqlStr, IDataParameter[] parameters, CommandType cmdType)
        {
            int count = -1;
            IDbConnection conn = CreateConnection();
            try
            {
                conn.Open();
                IDbCommand cmd = CreateCommand(sqlStr, cmdType, conn);
                SetCommandParams(cmd, parameters);
                count = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return count;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion

        #region ExecuteReader
        /// <summary>
        /// 执行带参数的Sql查询返回一个IDataReader
        /// </summary>
        /// <param name="sqlStr">Sql语句或存储过程名字</param>
        /// <param name="parameters">继承IDbDataParameter接口的对象实例的参数列表</param>
        /// <param name="cmdType">Command类型</param>
        /// <returns>返回一个继承IDataReader接口的对象实例</returns>
        public IDataReader ExecuteReader(string sqlStr, IDataParameter[] parameters, CommandType cmdType)
        {
            IDbConnection conn = CreateConnection();
            try
            {
                conn.Open();
                IDbCommand cmd = CreateCommand(sqlStr, cmdType, conn);
                SetCommandParams(cmd, parameters);
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        #endregion

        #region ExecuteDataSet
        /// <summary>
        /// 执行带参数的Sql查询返回一个DataSet
        /// </summary>
        /// <param name="sqlStr">Sql语句或存储过程名字</param>
        /// <param name="parameters">参数列表</param>
        /// <param name="cmdType">Command类型</param>
        /// <returns>返回一个DataSet</returns>
        public DataSet ExecuteDataSet(string sqlStr, IDataParameter[] parameters, CommandType cmdType)
        {
            IDbConnection conn = CreateConnection();
            try
            {
                conn.Open();
                IDbCommand cmd = CreateCommand(sqlStr, cmdType, conn);
                SetCommandParams(cmd, parameters);
                IDataAdapter ida = CreateDataAdapter();
                DataSet ds = (DataSet)Activator.CreateInstance(typeof(DataSet));
                ida.Fill(ds);
                return ds;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion

        #region ExecuteScalar
        /// <summary>
        /// 执行带参数的Sql查询返回结果的第一行第一列
        /// </summary>
        /// <param name="sqlStr">Sql语句或存储过程名字</param>
        /// <param name="parameters">参数列表</param>
        /// <param name="cmdType">Command类型</param>
        /// <returns>返回结果的第一行第一列</returns>
        public object ExecuteScalar(string sqlStr, IDataParameter[] parameters, CommandType cmdType)
        {
            object result;
            IDbConnection conn = CreateConnection();
            try
            {
                conn.Open();
                IDbCommand cmd = CreateCommand(sqlStr, cmdType, conn);
                SetCommandParams(cmd, parameters);
                result = cmd.ExecuteScalar();
                return result;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion

        #region 获取指定连接类型的数据库对象的类型
        /// <summary>
        /// 获取指定连接类型的数据库对象的类型
        /// </summary>
        /// <param name="className">数据库对象(不包括前缀)</param>
        /// <returns>返回数据库对象的类型</returns>
        private Type GetType(string className)
        {
            string assemblyPath = string.Empty;
            string classNamePrefix = string.Empty;
            switch (ConnectionInfo.ConnectionType)
            {
                case DBConnectionType.SqlServer:
                    assemblyPath = Assembly.GetAssembly(typeof(SqlConnection)).ToString();
                    classNamePrefix = "System.Data.SqlClient.Sql";
                    break;
                case DBConnectionType.MySql:
                    assemblyPath = Assembly.GetAssembly(typeof(MySqlConnection)).ToString();
                    classNamePrefix = "MySql.Data.MySqlClient.MySql";
                    break;
                case DBConnectionType.Oracle:
                    assemblyPath = Assembly.GetAssembly(typeof(OracleConnection)).ToString();
                    classNamePrefix = "System.Data.OracleClient.Oracle";
                    break;
                case DBConnectionType.OleDb:
                    assemblyPath = Assembly.GetAssembly(typeof(OleDbConnection)).ToString();
                    classNamePrefix = "System.Data.OleDb.OleDb";
                    break;
                case DBConnectionType.Odbc:
                    assemblyPath = Assembly.GetAssembly(typeof(OdbcConnection)).ToString();
                    classNamePrefix = "System.Data.Odbc.Odbc";
                    break;
            }
            return Assembly.Load(assemblyPath).GetType(classNamePrefix + className);
        }
        #endregion

        #region 创建继承IDbConnection接口的对象实例
        /// <summary> 
        /// 根据连接字符串创建继承IDbConnection接口的对象实例
        /// </summary> 
        /// <returns>返回继承IDbConnection接口的对象实例</returns> 
        public IDbConnection CreateConnection()
        {
            return Activator.CreateInstance(GetType("Connection"), ConnectionInfo.ConnectionString) as IDbConnection;
        }
        #endregion

        #region 创建继承IDbCommand接口的对象实例
        /// <summary> 
        /// 创建继承IDbCommand接口的对象实例
        /// </summary> 
        /// <returns>返回继承IDbCommand接口的对象实例</returns> 
        public IDbCommand CreateCommand(DBConnectionInfo connInfo)
        {
            return Activator.CreateInstance(GetType("Command")) as IDbCommand;
        }

        /// <summary>
        /// 创建继承IDbCommand接口的对象实例
        /// </summary>
        /// <param name="sqlStr">Sql语句</param>
        /// <param name="conn">继承IDbConnection接口的数据库连接对象实例</param>
        /// <returns>返回继承IDbCommand接口的对象实例</returns>
        public IDbCommand CreateCommand(string sqlStr, IDbConnection conn)
        {
            return Activator.CreateInstance(GetType("Command"), sqlStr, conn) as IDbCommand;
        }

        /// <summary>
        /// 创建继承IDbCommand接口的对象实例
        /// </summary>
        /// <param name="sqlStr">Sql语句</param>
        /// <param name="type">Command类型</param>
        /// <param name="conn">继承IDbConnection接口的数据库连接对象实例</param>
        /// <returns>返回继承IDbCommand接口的对象实例</returns>
        public IDbCommand CreateCommand(string sqlStr, CommandType type, IDbConnection conn)
        {
            IDbCommand iCmd = CreateCommand(sqlStr, conn);
            iCmd.CommandType = type;
            return iCmd;
        }
        #endregion

        #region 创建继承IDbDataParameter接口的对象实例
        /// <summary>
        /// 创建继承IDbDataParameter接口的对象实例
        /// </summary>
        /// <returns>返回继承IDbDataParameter接口的对象实例</returns>
        public IDbDataParameter CreateParameter()
        {
            return Activator.CreateInstance(GetType("Parameter")) as IDbDataParameter;
        }

        /// <summary>
        /// 创建继承IDbDataParameter接口的对象实例
        /// </summary>
        /// <param name="parameterName">参数名字 以@开头的字符串</param>
        /// <param name="value">参数值</param>
        /// <returns>返回继承IDbDataParameter接口的对象实例</returns>
        public IDbDataParameter CreateParameter(string parameterName, object value)
        {
            return Activator.CreateInstance(GetType("Parameter"), value) as IDbDataParameter;
        }

        /// <summary>
        /// 创建继承IDbDataParameter接口的对象实例
        /// </summary>
        /// <param name="parameterName">参数名字 以@开头的字符串</param>
        /// <param name="type">参数数据类型</param>
        /// <param name="size">参数数据大小</param>
        /// <returns>返回继承IDbDataParameter接口的对象实例</returns>
        public IDbDataParameter CreateParameter(string parameterName, DbType type, int size)
        {
            IDbDataParameter iParam = CreateParameter();
            iParam.ParameterName = parameterName;
            iParam.DbType = type;
            iParam.Size = size;
            return iParam;
        }

        /// <summary>
        /// 创建继承IDbDataParameter接口的对象实例
        /// </summary>
        /// <param name="parameterName">参数名字 以@开头的字符串</param>
        /// <param name="type">参数数据类型</param>
        /// <param name="size">参数数据大小</param>
        /// <param name="value">参数值</param>
        /// <returns>返回继承IDbDataParameter接口的对象实例</returns>
        public IDbDataParameter CreateParameter(string parameterName, DbType type, int size, object value)
        {
            IDbDataParameter iParam = CreateParameter(parameterName, type, size);
            if (value != null)
            {
                iParam.Value = value;
            }
            return iParam;
        }

        /// <summary>
        /// 创建继承IDbDataParameter接口的对象实例
        /// </summary>
        /// <param name="parameterName">参数名字 以@开头的字符串</param>
        /// <param name="type">参数数据类型</param>
        /// <param name="size">参数数据大小</param>
        /// <param name="dir">参数流向</param>
        /// <param name="value">参数值</param>
        /// <returns>返回继承IDbDataParameter接口的对象实例</returns>
        public IDbDataParameter CreateParameter(string parameterName, DbType type, int size, ParameterDirection dir, object value)
        {
            IDbDataParameter iParam = CreateParameter(parameterName, type, size, value);
            iParam.Direction = dir;
            return iParam;
        }
        #endregion

        #region 创建继承IDbDataAdapter接口的对象实例
        /// <summary> 
        /// 创建继承IDbDataAdapter接口的对象实例
        /// </summary> 
        /// <returns>返回继承IDbDataAdapter接口的对象实例</returns> 
        public IDbDataAdapter CreateDataAdapter()
        {
            return Activator.CreateInstance(GetType("DataAdapter")) as IDbDataAdapter;
        }
        #endregion

        #region ValidateDataRow
        /// <summary>
        /// 从一个DataRow中，安全得到列colname中的值：值为字符串类型
        /// </summary>
        /// <param name="row">数据行对象</param>
        /// <param name="colName">列名</param>
        /// <returns>如果值存在，返回；否则，返回System.String.Empty</returns>
        public static string ValidateDataRow_S(DataRow row, string colName)
        {
            if (row[colName] != DBNull.Value)
                return row[colName].ToString();
            else
                return System.String.Empty;
        }

        /// <summary>
        /// 从一个DataRow中，安全得到列colname中的值：值为整数类型
        /// </summary>
        /// <param name="row">数据行对象</param>
        /// <param name="colName">列名</param>
        /// <returns>如果值存在，返回；否则，返回System.Int32.MinValue</returns>
        public static int ValidateDataRow_N(DataRow row, string colName)
        {
            if (row[colName] != DBNull.Value)
                return Convert.ToInt32(row[colName]);
            else
                return System.Int32.MinValue;
        }

        /// <summary>
        /// 从一个DataRow中，安全得到列colname中的值：值为布尔类型
        /// </summary>
        /// <param name="row">数据行对象</param>
        /// <param name="colName">列名</param>
        /// <returns>如果值存在，返回；否则，返回System.Int32.MinValue</returns>
        public static bool ValidateDataRow_R(DataRow row, string colName)
        {
            if (row[colName] != DBNull.Value)
                return Convert.ToBoolean(row[colName]);
            else
                return false;
        }

        /// <summary>
        /// 从一个DataRow中，安全得到列colname中的值：值为浮点数类型
        /// </summary>
        /// <param name="row">数据行对象</param>
        /// <param name="colName">列名</param>
        /// <returns>如果值存在，返回；否则，返回System.Double.MinValue</returns>
        public static double ValidateDataRow_F(DataRow row, string colName)
        {
            if (row[colName] != DBNull.Value)
                return Convert.ToDouble(row[colName]);
            else
                return System.Double.MinValue;
        }

        /// <summary>
        /// 从一个DataRow中，安全得到列colname中的值：值为时间类型
        /// </summary>
        /// <param name="row">数据行对象</param>
        /// <param name="colName">列名</param>
        /// <returns>如果值存在，返回；否则，返回System.DateTime.MinValue;</returns>
        public static DateTime ValidateDataRow_T(DataRow row, string colName)
        {
            if (row[colName] != DBNull.Value)
                return Convert.ToDateTime(row[colName]);
            else
                return System.DateTime.MinValue;
        }
        #endregion
    }
}
